<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>sql数据查询之---嵌套查询 | 路漫漫其修远兮，吾将上下而求索</title><meta name="keywords" content="上课笔记,sql语句"><meta name="author" content="Charles Yan"><meta name="copyright" content="Charles Yan"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="嵌套查询：1.带有in谓词的子查询2.带有比较运算符的子查询3.带有ANY(SOME)或ALL谓词的子查询4.带有EXISTS谓词的子查询">
<meta property="og:type" content="article">
<meta property="og:title" content="sql数据查询之---嵌套查询">
<meta property="og:url" content="https://www.charlesyan.cn/2020/04/02/sql%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2---%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2/index.html">
<meta property="og:site_name" content="路漫漫其修远兮，吾将上下而求索">
<meta property="og:description" content="嵌套查询：1.带有in谓词的子查询2.带有比较运算符的子查询3.带有ANY(SOME)或ALL谓词的子查询4.带有EXISTS谓词的子查询">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1585844481722&di=485b7474adde6d0184955ebc5fc2d444&imgtype=0&src=http%3A%2F%2Fedu-image.nosdn.127.net%2FCDBAE5A1C407E210A67054D2A3BC52B2.jpg%3FimageView%26thumbnail%3D510y288%26quality%3D100">
<meta property="article:published_time" content="2020-04-02T13:33:48.039Z">
<meta property="article:modified_time" content="2020-08-17T01:59:21.465Z">
<meta property="article:author" content="Charles Yan">
<meta property="article:tag" content="上课笔记">
<meta property="article:tag" content="sql语句">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1585844481722&di=485b7474adde6d0184955ebc5fc2d444&imgtype=0&src=http%3A%2F%2Fedu-image.nosdn.127.net%2FCDBAE5A1C407E210A67054D2A3BC52B2.jpg%3FimageView%26thumbnail%3D510y288%26quality%3D100"><link rel="shortcut icon" href="/img/favicon.ico"><link rel="canonical" href="https://www.charlesyan.cn/2020/04/02/sql%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2---%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: {"path":"search.xml","languages":{"hits_empty":"找不到您查询的内容：${query}"}},
  translate: {"defaultEncoding":2,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"简"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: true,
    post: false
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'mediumZoom',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"bottom-left"},
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isanchor: true
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'sql数据查询之---嵌套查询',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2020-08-17 09:59:21'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const fontSizeVal = saveToLocal.get('global-font-size')
    if (fontSizeVal !== undefined) {
      document.documentElement.style.setProperty('--global-font-size', fontSizeVal + 'px')
    }
    })(window)</script><link rel="stylesheet" href="/css/selfdesign.css" media="defer" onload="this.media='all'"><meta name="generator" content="Hexo 5.4.0"></head><body><div id="loading-box"><div class="loading-left-bg"></div><div class="loading-right-bg"></div><div class="spinner-box"><div class="configure-border-1"><div class="configure-core"></div></div><div class="configure-border-2"><div class="configure-core"></div></div><div class="loading-word">加载中...</div></div></div><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="/img/My%20avatar.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">37</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">22</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">12</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fa fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fa fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fa fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-coffee"></i><span> 留言板</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fa fa-link"></i><span> 友情链接</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-heart"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fa fa-list"></i><span> 清单</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/music/"><i class="fa-fw fa fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/movies/"><i class="fa-fw fa fa-film"></i><span> 电影</span></a></li><li><a class="site-page child" href="/Gallery/"><i class="fa-fw fas fa-images"></i><span> 照片</span></a></li></ul></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://timgsa.baidu.com/timg?image&amp;quality=80&amp;size=b9999_10000&amp;sec=1585844481722&amp;di=485b7474adde6d0184955ebc5fc2d444&amp;imgtype=0&amp;src=http%3A%2F%2Fedu-image.nosdn.127.net%2FCDBAE5A1C407E210A67054D2A3BC52B2.jpg%3FimageView%26thumbnail%3D510y288%26quality%3D100')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">路漫漫其修远兮，吾将上下而求索</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fa fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fa fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fa fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-coffee"></i><span> 留言板</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fa fa-link"></i><span> 友情链接</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-heart"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fa fa-list"></i><span> 清单</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/music/"><i class="fa-fw fa fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/movies/"><i class="fa-fw fa fa-film"></i><span> 电影</span></a></li><li><a class="site-page child" href="/Gallery/"><i class="fa-fw fas fa-images"></i><span> 照片</span></a></li></ul></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">sql数据查询之---嵌套查询</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2020-04-02T13:33:48.039Z" title="发表于 2020-04-02 21:33:48">2020-04-02</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2020-08-17T01:59:21.465Z" title="更新于 2020-08-17 09:59:21">2020-08-17</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E4%B8%8A%E8%AF%BE%E7%AC%94%E8%AE%B0/">上课笔记</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">1.4k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>5分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="sql数据查询之---嵌套查询"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h1 id="sql数据查询之———嵌套查询"><a href="#sql数据查询之———嵌套查询" class="headerlink" title="sql数据查询之———嵌套查询"></a>sql数据查询之———嵌套查询</h1><h2 id="一、嵌套查询概述"><a href="#一、嵌套查询概述" class="headerlink" title="一、嵌套查询概述"></a>一、嵌套查询概述</h2><ul>
<li>一个 SELECT-FROM-WHERE 语句称为一个查询块。</li>
<li>将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询称为 嵌套查询。</li>
</ul>
<p>例如：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sname	                           <span class="comment">/*外层查询/父查询*/</span></span><br><span class="line">     <span class="keyword">FROM</span> Student</span><br><span class="line">     <span class="keyword">WHERE</span> Sno <span class="keyword">IN</span></span><br><span class="line">                        ( <span class="keyword">SELECT</span> Sno        <span class="comment">/*内层查询/子查询*/</span></span><br><span class="line">                          <span class="keyword">FROM</span> SC</span><br><span class="line">                          <span class="keyword">WHERE</span> Cno<span class="operator">=</span> <span class="string">&#x27; 2 &#x27;</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p><strong>注意：子查询的SELECT语句中不能使用 ORDER BY 子句，因为 ORDER BY 子句只能对最终查询结果排序。</strong></p>
<ul>
<li>不相关子查询：<strong>子查询的查询条件不依赖于父查询</strong></li>
<li>相关子查询：<strong>子查询的查询条件依赖于父查询</strong></li>
</ul>
<p>（后续在对二者详细解释）</p>
<h2 id="二、嵌套查询"><a href="#二、嵌套查询" class="headerlink" title="二、嵌套查询"></a>二、嵌套查询</h2><h3 id="1-带有in谓词的子查询"><a href="#1-带有in谓词的子查询" class="headerlink" title="1.带有in谓词的子查询"></a>1.带有in谓词的子查询</h3><h4 id="例1：查询与“刘晨”在同一个系学习的学生。"><a href="#例1：查询与“刘晨”在同一个系学习的学生。" class="headerlink" title="例1：查询与“刘晨”在同一个系学习的学生。"></a>例1：<strong>查询与“刘晨”在同一个系学习的学生。</strong></h4><p>分部完成：</p>
<p><strong>①</strong> 确定“刘晨”所在系名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span>  Sdept  </span><br><span class="line">         <span class="keyword">FROM</span>     Student                            </span><br><span class="line">         <span class="keyword">WHERE</span>  Sname<span class="operator">=</span> <span class="string">&#x27; 刘晨 &#x27;</span>;</span><br><span class="line">				</span><br></pre></td></tr></table></figure>
<p>假设结果为CS</p>
<p><strong>②</strong> 查找所有在CS系学习的学生。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span>   Sno, Sname, Sdept     </span><br><span class="line">        <span class="keyword">FROM</span>      Student                 </span><br><span class="line">        <span class="keyword">WHERE</span>   Sdept<span class="operator">=</span> <span class="string">&#x27; CS &#x27;</span>; </span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>第一部就是子查询，第二步为父查询</p>
<p>那么可以写成：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sno, Sname, Sdept</span><br><span class="line">    	<span class="keyword">FROM</span> Student</span><br><span class="line">   	<span class="keyword">WHERE</span> Sdept  <span class="keyword">IN</span></span><br><span class="line">                  (<span class="keyword">SELECT</span> Sdept</span><br><span class="line">                   <span class="keyword">FROM</span> Student</span><br><span class="line">                   <span class="keyword">WHERE</span> Sname<span class="operator">=</span> <span class="string">&#x27; 刘晨 &#x27;</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>注：这是不相关查询，即子查询条件与父查询条件无关</p>
<h4 id="例2：查询选修了课程名为“信息系统”的学生学号和姓名"><a href="#例2：查询选修了课程名为“信息系统”的学生学号和姓名" class="headerlink" title="例2：查询选修了课程名为“信息系统”的学生学号和姓名"></a>例2：<strong>查询选修了课程名为“信息系统”的学生学号和姓名</strong></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sno,Sname                 ③ 最后在Student关系中</span><br><span class="line">  	<span class="keyword">FROM</span>    Student                          取出Sno和Sname</span><br><span class="line"> 	<span class="keyword">WHERE</span> Sno  <span class="keyword">IN</span></span><br><span class="line">             (<span class="keyword">SELECT</span> Sno                     ② 然后在SC关系中找出选</span><br><span class="line">              <span class="keyword">FROM</span>    SC                         修了<span class="number">3</span>号课程的学生学号</span><br><span class="line">              <span class="keyword">WHERE</span>  Cno <span class="keyword">IN</span></span><br><span class="line">                     (<span class="keyword">SELECT</span> Cno             ① 首先在Course关系中找出</span><br><span class="line">                       <span class="keyword">FROM</span> Course           “信息系统”的课程号，为<span class="number">3</span>号</span><br><span class="line">                       <span class="keyword">WHERE</span> Cname<span class="operator">=</span> <span class="string">&#x27;信息系统&#x27;</span>                      </span><br><span class="line">		          )</span><br><span class="line">        );</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p>当然也可以用连接查询实现:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Student.Sno,Sname</span><br><span class="line"><span class="keyword">FROM</span> Student,SC,Course</span><br><span class="line"><span class="keyword">WHERE</span> Student.Sno<span class="operator">=</span>SC.Sno <span class="keyword">AND</span></span><br><span class="line">    SC.Cno<span class="operator">=</span>Course.Cno <span class="keyword">AND</span></span><br><span class="line">    Course.Cname<span class="operator">=</span><span class="string">&#x27;信息系统&#x27;</span>;</span><br></pre></td></tr></table></figure>
<h3 id="2-带有比较运算符的子查询"><a href="#2-带有比较运算符的子查询" class="headerlink" title="2.带有比较运算符的子查询"></a>2.<strong>带有比较运算符的子查询</strong></h3><p>当能确切知道内层查询返回单值时，可用比较运算符（&gt;，&lt;，=，&gt;=，&lt;=，!=或&lt; &gt;）。</p>
<p>对于第一个例子来说，由于一个学生只可能在一个系里学习，所以可以用 = 代替 in</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sno, Sname, Sdept</span><br><span class="line">    	<span class="keyword">FROM</span> Student</span><br><span class="line">   	<span class="keyword">WHERE</span> Sdept  <span class="operator">=</span></span><br><span class="line">                  (<span class="keyword">SELECT</span> Sdept</span><br><span class="line">                   <span class="keyword">FROM</span> Student</span><br><span class="line">                   <span class="keyword">WHERE</span> Sname<span class="operator">=</span> <span class="string">&#x27; 刘晨 &#x27;</span>);</span><br></pre></td></tr></table></figure>
<h3 id="3-带有ANY-SOME-或ALL谓词的子查询"><a href="#3-带有ANY-SOME-或ALL谓词的子查询" class="headerlink" title="3.带有ANY(SOME)或ALL谓词的子查询"></a>3.带有ANY(SOME)或ALL谓词的子查询</h3><p><strong>子查询返回单值时可以用比较运算符，但返回多值时要用ANY（有的系统用SOME）或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符。</strong>其语义如下：</p>
<div class="table-container">
<table>
<thead>
<tr>
<th>&gt;ANY</th>
<th>大于子查询结果中的某个值</th>
</tr>
</thead>
<tbody>
<tr>
<td>&gt;ALL</td>
<td>大于子查询结果中的所有值</td>
</tr>
<tr>
<td>&lt;ANY</td>
<td>小于子查询结果中的某个值</td>
</tr>
<tr>
<td>&lt;ALL</td>
<td>小于子查询结果中的所有值</td>
</tr>
<tr>
<td>&gt;=ANY</td>
<td>大于等于子查询结果中的某个值</td>
</tr>
<tr>
<td>&gt;=ALL</td>
<td>大于等于子查询结果中的所有值</td>
</tr>
<tr>
<td>&lt;=ALL</td>
<td>小于等于子查询结果中的所有值</td>
</tr>
<tr>
<td>&lt;=ANY</td>
<td>大于等于子查询结果中的某个值</td>
</tr>
<tr>
<td>=ANY</td>
<td>等于子查询结果中的某个值</td>
</tr>
<tr>
<td>=ALL</td>
<td>等于子查询结果中的所有值（通常没有实际意义）</td>
</tr>
<tr>
<td>!=（或&lt;&gt;）ANY</td>
<td>不等于子查询结果中的某个值</td>
</tr>
<tr>
<td>!=（或&lt;&gt;）ALL</td>
<td>不等于子查询结果中的任何一个值</td>
</tr>
</tbody>
</table>
</div>
<h4 id="例1：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄"><a href="#例1：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄" class="headerlink" title="例1：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄"></a>例1：<strong>查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄</strong></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sname,Sage</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> Sage<span class="operator">&lt;</span><span class="keyword">ANY</span> (<span class="keyword">SELECT</span> Sage</span><br><span class="line">                <span class="keyword">FROM</span> Student</span><br><span class="line">                <span class="keyword">WHERE</span> Sdept<span class="operator">=</span><span class="string">&#x27;CS&#x27;</span>)</span><br><span class="line"><span class="keyword">AND</span> Sdept <span class="operator">&lt;&gt;</span> <span class="string">&#x27;CS&#x27;</span>;</span><br></pre></td></tr></table></figure>
<h4 id="例2：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄"><a href="#例2：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄" class="headerlink" title="例2：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄"></a>例2：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sname,Sage</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> Sage<span class="operator">&lt;</span><span class="keyword">ALL</span></span><br><span class="line">          (<span class="keyword">SELECT</span> Sage</span><br><span class="line">           <span class="keyword">FROM</span> Student</span><br><span class="line">           <span class="keyword">WHERE</span> Sdept<span class="operator">=</span><span class="string">&#x27;CS&#x27;</span>)</span><br><span class="line"><span class="keyword">AND</span> Sdept <span class="operator">&lt;&gt;</span> <span class="string">&#x27;CS&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p>提示：本查询同样可以用<strong>聚集函数</strong>实现</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sname,Sage</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> Sage <span class="operator">&lt;</span></span><br><span class="line">    (<span class="keyword">SELECT</span> <span class="built_in">MIN</span>(Sage)</span><br><span class="line">     <span class="keyword">FROM</span> Student</span><br><span class="line">     <span class="keyword">WHERE</span> Sdept<span class="operator">=</span><span class="string">&#x27;CS&#x27;</span>)</span><br><span class="line"><span class="keyword">AND</span> Sdept <span class="operator">&lt;&gt;</span><span class="string">&#x27;CS&#x27;</span>;</span><br></pre></td></tr></table></figure>
<p><img src="https://i.loli.net/2020/04/02/pa3jZ8t5SCVIAHy.png" alt="截屏2020-04-02下午9.25.09.png"></p>
<h3 id="4-带有EXISTS谓词的子查询"><a href="#4-带有EXISTS谓词的子查询" class="headerlink" title="4.带有EXISTS谓词的子查询"></a>4.带有EXISTS谓词的子查询</h3><p><strong>带有EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。</strong></p>
<h4 id="例1：-查询所有选修了1号课程的学生姓名"><a href="#例1：-查询所有选修了1号课程的学生姓名" class="headerlink" title="例1： 查询所有选修了1号课程的学生姓名"></a>例1： 查询所有选修了1号课程的学生姓名</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sname</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">EXISTS</span></span><br><span class="line">    (<span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line">     <span class="keyword">FROM</span> SC</span><br><span class="line">     <span class="keyword">WHERE</span> Sno<span class="operator">=</span>Student.Sno <span class="keyword">AND</span> Cno<span class="operator">=</span><span class="string">&#x27;1&#x27;</span>);</span><br></pre></td></tr></table></figure>
<ul>
<li><strong>本查询涉及Student和SC关系</strong></li>
<li><strong>在Student中依次取每个元组的Sno值，用此值去检查SC表</strong></li>
<li><strong>若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= ‘1’，则取此Student.Sname送入结果表</strong></li>
</ul>
<h4 id="例2：查询没有选修1号课程的学生姓名"><a href="#例2：查询没有选修1号课程的学生姓名" class="headerlink" title="例2：查询没有选修1号课程的学生姓名"></a>例2：查询没有选修1号课程的学生姓名</h4><p>直接在<strong>EXISTS</strong>前加<strong>NOT</strong></p>
<p><strong>重难点</strong>：</p>
<h4 id="例3：查询选修了全部课程的学生姓名"><a href="#例3：查询选修了全部课程的学生姓名" class="headerlink" title="例3：查询选修了全部课程的学生姓名"></a>例3：查询选修了全部课程的学生姓名</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> Sname</span><br><span class="line"><span class="keyword">FROM</span> Student</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">NOT</span> <span class="keyword">EXISTS</span></span><br><span class="line">    (<span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line">     <span class="keyword">FROM</span> Course</span><br><span class="line">     <span class="keyword">WHERE</span> <span class="keyword">NOT</span> <span class="keyword">EXISTS</span></span><br><span class="line">        (<span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line">         <span class="keyword">FROM</span> SC</span><br><span class="line">         <span class="keyword">WHERE</span> Sno<span class="operator">=</span>Student.Sno</span><br><span class="line">            <span class="keyword">AND</span> Cno<span class="operator">=</span>Course.Cno));</span><br></pre></td></tr></table></figure>
<p><strong>用EXISTS/NOT EXISTS实现全称量词（难点）</strong></p>
<ul>
<li>SQL语言中没有全称量词$\forall$（For all）</li>
<li>可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：$ (\forall x)P = ﹁(\exist x(﹁P))$</li>
</ul>
<h4 id="例4-：查询至少选修了学生201215122选修的全部课程的学生号码"><a href="#例4-：查询至少选修了学生201215122选修的全部课程的学生号码" class="headerlink" title="例4 ：查询至少选修了学生201215122选修的全部课程的学生号码"></a>例4 ：查询至少选修了学生201215122选修的全部课程的学生号码</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> Sno</span><br><span class="line"><span class="keyword">FROM</span> SC SCX</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">NOT</span> <span class="keyword">EXISTS</span></span><br><span class="line">    (<span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line">     <span class="keyword">FROM</span> SC SCY</span><br><span class="line">     <span class="keyword">WHERE</span> SCY.Sno<span class="operator">=</span><span class="string">&#x27;201215122&#x27;</span> <span class="keyword">AND</span></span><br><span class="line">        <span class="keyword">NOT</span> <span class="keyword">EXISTS</span></span><br><span class="line">        (<span class="keyword">SELECT</span> <span class="operator">*</span></span><br><span class="line">         <span class="keyword">FROM</span> SC SCZ</span><br><span class="line">         <span class="keyword">WHERE</span> SCZ.Sno<span class="operator">=</span>SCX.Sno <span class="keyword">AND</span></span><br><span class="line">         SCZ.Cno<span class="operator">=</span>SCY.Cno));</span><br></pre></td></tr></table></figure>
<h2 id="三、总结"><a href="#三、总结" class="headerlink" title="三、总结"></a>三、总结</h2><p><strong>不同形式的查询间的替换</strong></p>
<p>一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换</p>
<p>所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带<strong>EXISTS</strong>谓词的子查询等价替换</p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">Charles Yan</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://www.charlesyan.cn/2020/04/02/sql%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2---%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2/">https://www.charlesyan.cn/2020/04/02/sql%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2---%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://www.charlesyan.cn" target="_blank">路漫漫其修远兮，吾将上下而求索</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/%E4%B8%8A%E8%AF%BE%E7%AC%94%E8%AE%B0/">上课笔记</a><a class="post-meta__tags" href="/tags/sql%E8%AF%AD%E5%8F%A5/">sql语句</a></div><div class="post_share"><div class="social-share" data-image="https://timgsa.baidu.com/timg?image&amp;quality=80&amp;size=b9999_10000&amp;sec=1585844481722&amp;di=485b7474adde6d0184955ebc5fc2d444&amp;imgtype=0&amp;src=http%3A%2F%2Fedu-image.nosdn.127.net%2FCDBAE5A1C407E210A67054D2A3BC52B2.jpg%3FimageView%26thumbnail%3D510y288%26quality%3D100" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="post-reward"><div class="reward-button button--animated"><i class="fas fa-qrcode"></i> 打赏</div><div class="reward-main"><ul class="reward-all"><li class="reward-item"><a href="/img/IMG_0003.jpeg" target="_blank"><img class="post-qr-code-img" src="/img/IMG_0003.jpeg" alt="wechat"/></a><div class="post-qr-code-desc">wechat</div></li><li class="reward-item"><a href="/img/IMG_0004.jpeg" target="_blank"><img class="post-qr-code-img" src="/img/IMG_0004.jpeg" alt="alipay"/></a><div class="post-qr-code-desc">alipay</div></li></ul></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/2020/04/18/Datagrip%E7%9A%84%E5%9F%BA%E6%9C%AC%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95/"><img class="prev-cover" src="https://timgsa.baidu.com/timg?image&amp;quality=80&amp;size=b9999_10000&amp;sec=1585844481722&amp;di=485b7474adde6d0184955ebc5fc2d444&amp;imgtype=0&amp;src=http%3A%2F%2Fedu-image.nosdn.127.net%2FCDBAE5A1C407E210A67054D2A3BC52B2.jpg%3FimageView%26thumbnail%3D510y288%26quality%3D100" onerror="onerror=null;src='/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">Datagrip的基本使用方法。</div></div></a></div><div class="next-post pull-right"><a href="/2020/02/24/markdown%E8%AF%AD%E6%B3%95/"><img class="next-cover" src="https://ss2.bdstatic.com/70cFvnSh_Q1YnxGkpoWK1HF6hhy/it/u=1613194509,3683268681&amp;fm=26&amp;gp=0.jpg" onerror="onerror=null;src='/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">markdown语法，由浅入深，最全攻略，笔记。</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span> 相关推荐</span></div><div class="relatedPosts-list"><div><a href="/2020/02/24/markdown语法/" title="markdown语法，由浅入深，最全攻略，笔记。"><img class="cover" src="https://ss2.bdstatic.com/70cFvnSh_Q1YnxGkpoWK1HF6hhy/it/u=1613194509,3683268681&fm=26&gp=0.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-02-24</div><div class="title">markdown语法，由浅入深，最全攻略，笔记。</div></div></a></div><div><a href="/2021/06/24/信息系统安全实训第四天/" title="信息系统安全实训第四天"><img class="cover" src="https://gitee.com/yan_zilong/picgo/raw/master/img/Blog/post/密码学复习提纲/3989.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-06-24</div><div class="title">信息系统安全实训第四天</div></div></a></div><div><a href="/2021/06/23/信息系统安全实训第三天/" title="信息系统安全实训第三天"><img class="cover" src="https://gitee.com/yan_zilong/picgo/raw/master/img/Blog/post/密码学复习提纲/3989.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-06-23</div><div class="title">信息系统安全实训第三天</div></div></a></div><div><a href="/2021/06/22/第二天课上笔记/" title="docker的简单命令使用"><img class="cover" src="https://gitee.com/yan_zilong/picgo/raw/master/img/Blog/post/密码学复习提纲/3989.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-06-22</div><div class="title">docker的简单命令使用</div></div></a></div><div><a href="/2021/06/22/信息系统安全实训第二天/" title="信息系统安全实训第二天"><img class="cover" src="https://gitee.com/yan_zilong/picgo/raw/master/img/Blog/post/密码学复习提纲/7801.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-06-22</div><div class="title">信息系统安全实训第二天</div></div></a></div><div><a href="/2021/06/21/玩转勒索病毒/" title="信息系统安全实训第一天"><img class="cover" src="https://gitee.com/yan_zilong/picgo/raw/master/img/Blog/post/密码学复习提纲/3989.jpg" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-06-21</div><div class="title">信息系统安全实训第一天</div></div></a></div></div></div><hr/><div id="post-comment"><div class="comment-head"><div class="comment-headline"><i class="fas fa-comments fa-fw"></i><span> 评论</span></div></div><div class="comment-wrap"><div><div id="twikoo-wrap"></div></div></div></div></div><div class="aside-content" id="aside-content"><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#sql%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2%E4%B9%8B%E2%80%94%E2%80%94%E2%80%94%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.</span> <span class="toc-text">sql数据查询之———嵌套查询</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%80%E3%80%81%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2%E6%A6%82%E8%BF%B0"><span class="toc-number">1.1.</span> <span class="toc-text">一、嵌套查询概述</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.2.</span> <span class="toc-text">二、嵌套查询</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E5%B8%A6%E6%9C%89in%E8%B0%93%E8%AF%8D%E7%9A%84%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.2.1.</span> <span class="toc-text">1.带有in谓词的子查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BE%8B1%EF%BC%9A%E6%9F%A5%E8%AF%A2%E4%B8%8E%E2%80%9C%E5%88%98%E6%99%A8%E2%80%9D%E5%9C%A8%E5%90%8C%E4%B8%80%E4%B8%AA%E7%B3%BB%E5%AD%A6%E4%B9%A0%E7%9A%84%E5%AD%A6%E7%94%9F%E3%80%82"><span class="toc-number">1.2.1.1.</span> <span class="toc-text">例1：查询与“刘晨”在同一个系学习的学生。</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BE%8B2%EF%BC%9A%E6%9F%A5%E8%AF%A2%E9%80%89%E4%BF%AE%E4%BA%86%E8%AF%BE%E7%A8%8B%E5%90%8D%E4%B8%BA%E2%80%9C%E4%BF%A1%E6%81%AF%E7%B3%BB%E7%BB%9F%E2%80%9D%E7%9A%84%E5%AD%A6%E7%94%9F%E5%AD%A6%E5%8F%B7%E5%92%8C%E5%A7%93%E5%90%8D"><span class="toc-number">1.2.1.2.</span> <span class="toc-text">例2：查询选修了课程名为“信息系统”的学生学号和姓名</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%B8%A6%E6%9C%89%E6%AF%94%E8%BE%83%E8%BF%90%E7%AE%97%E7%AC%A6%E7%9A%84%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.2.2.</span> <span class="toc-text">2.带有比较运算符的子查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E5%B8%A6%E6%9C%89ANY-SOME-%E6%88%96ALL%E8%B0%93%E8%AF%8D%E7%9A%84%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.2.3.</span> <span class="toc-text">3.带有ANY(SOME)或ALL谓词的子查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BE%8B1%EF%BC%9A%E6%9F%A5%E8%AF%A2%E9%9D%9E%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%A7%91%E5%AD%A6%E7%B3%BB%E4%B8%AD%E6%AF%94%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%A7%91%E5%AD%A6%E7%B3%BB%E4%BB%BB%E6%84%8F%E4%B8%80%E4%B8%AA%E5%AD%A6%E7%94%9F%E5%B9%B4%E9%BE%84%E5%B0%8F%E7%9A%84%E5%AD%A6%E7%94%9F%E5%A7%93%E5%90%8D%E5%92%8C%E5%B9%B4%E9%BE%84"><span class="toc-number">1.2.3.1.</span> <span class="toc-text">例1：查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BE%8B2%EF%BC%9A%E6%9F%A5%E8%AF%A2%E9%9D%9E%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%A7%91%E5%AD%A6%E7%B3%BB%E4%B8%AD%E6%AF%94%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%A7%91%E5%AD%A6%E7%B3%BB%E6%89%80%E6%9C%89%E5%AD%A6%E7%94%9F%E5%B9%B4%E9%BE%84%E9%83%BD%E5%B0%8F%E7%9A%84%E5%AD%A6%E7%94%9F%E5%A7%93%E5%90%8D%E5%92%8C%E5%B9%B4%E9%BE%84"><span class="toc-number">1.2.3.2.</span> <span class="toc-text">例2：查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名和年龄</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E5%B8%A6%E6%9C%89EXISTS%E8%B0%93%E8%AF%8D%E7%9A%84%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.2.4.</span> <span class="toc-text">4.带有EXISTS谓词的子查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BE%8B1%EF%BC%9A-%E6%9F%A5%E8%AF%A2%E6%89%80%E6%9C%89%E9%80%89%E4%BF%AE%E4%BA%861%E5%8F%B7%E8%AF%BE%E7%A8%8B%E7%9A%84%E5%AD%A6%E7%94%9F%E5%A7%93%E5%90%8D"><span class="toc-number">1.2.4.1.</span> <span class="toc-text">例1： 查询所有选修了1号课程的学生姓名</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BE%8B2%EF%BC%9A%E6%9F%A5%E8%AF%A2%E6%B2%A1%E6%9C%89%E9%80%89%E4%BF%AE1%E5%8F%B7%E8%AF%BE%E7%A8%8B%E7%9A%84%E5%AD%A6%E7%94%9F%E5%A7%93%E5%90%8D"><span class="toc-number">1.2.4.2.</span> <span class="toc-text">例2：查询没有选修1号课程的学生姓名</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BE%8B3%EF%BC%9A%E6%9F%A5%E8%AF%A2%E9%80%89%E4%BF%AE%E4%BA%86%E5%85%A8%E9%83%A8%E8%AF%BE%E7%A8%8B%E7%9A%84%E5%AD%A6%E7%94%9F%E5%A7%93%E5%90%8D"><span class="toc-number">1.2.4.3.</span> <span class="toc-text">例3：查询选修了全部课程的学生姓名</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E4%BE%8B4-%EF%BC%9A%E6%9F%A5%E8%AF%A2%E8%87%B3%E5%B0%91%E9%80%89%E4%BF%AE%E4%BA%86%E5%AD%A6%E7%94%9F201215122%E9%80%89%E4%BF%AE%E7%9A%84%E5%85%A8%E9%83%A8%E8%AF%BE%E7%A8%8B%E7%9A%84%E5%AD%A6%E7%94%9F%E5%8F%B7%E7%A0%81"><span class="toc-number">1.2.4.4.</span> <span class="toc-text">例4 ：查询至少选修了学生201215122选修的全部课程的学生号码</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%89%E3%80%81%E6%80%BB%E7%BB%93"><span class="toc-number">1.3.</span> <span class="toc-text">三、总结</span></a></li></ol></li></ol></div></div></div></div></main><footer id="footer" style="background-image: url('https://timgsa.baidu.com/timg?image&amp;quality=80&amp;size=b9999_10000&amp;sec=1585844481722&amp;di=485b7474adde6d0184955ebc5fc2d444&amp;imgtype=0&amp;src=http%3A%2F%2Fedu-image.nosdn.127.net%2FCDBAE5A1C407E210A67054D2A3BC52B2.jpg%3FimageView%26thumbnail%3D510y288%26quality%3D100')"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2021 By Charles Yan</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div><div class="footer_custom_text">Hi, welcome to my <a target="_blank" rel="noopener" href="https:charlesyan.cn/">blog</a>!</div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="font-plus" type="button" title="放大字体"><i class="fas fa-plus"></i></button><button id="font-minus" type="button" title="缩小字体"><i class="fas fa-minus"></i></button><button id="translateLink" type="button" title="简繁转换">简</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="chat_btn" type="button" title="rightside.chat_btn"><i class="fas fa-sms"></i></button><a id="to_comment" href="#post-comment" title="直达评论"><i class="fas fa-comments"></i></a><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="local-search"><div class="search-dialog"><div class="search-dialog__title" id="local-search-title">本地搜索</div><div id="local-input-panel"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div></div><hr/><div id="local-search-results"></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="/js/tw_cn.js"></script><script src="https://cdn.jsdelivr.net/npm/medium-zoom/dist/medium-zoom.min.js"></script><script src="https://cdn.jsdelivr.net/npm/instant.page/instantpage.min.js" type="module"></script><script src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><script>function panguFn () {
  if (typeof pangu === 'object') pangu.autoSpacingPage()
  else {
    getScript('https://cdn.jsdelivr.net/npm/pangu/dist/browser/pangu.min.js')
      .then(() => {
        pangu.autoSpacingPage()
      })
  }
}

function panguInit () {
  if (false){
    GLOBAL_CONFIG_SITE.isPost && panguFn()
  } else {
    panguFn()
  }
}

document.addEventListener('DOMContentLoaded', panguInit)</script><script src="/js/search/local-search.js"></script><script>var preloader = {
  endLoading: () => {
    document.body.style.overflow = 'auto';
    document.getElementById('loading-box').classList.add("loaded")
  },
  initLoading: () => {
    document.body.style.overflow = '';
    document.getElementById('loading-box').classList.remove("loaded")

  }
}
window.addEventListener('load',preloader.endLoading())</script><div class="js-pjax"><script>if (document.getElementsByClassName('mermaid').length) {
  if (window.mermaidJsLoad) mermaid.init()
  else {
    getScript('https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js').then(() => {
      window.mermaidJsLoad = true
      mermaid.initialize({
        theme: 'default',
      })
      false && mermaid.init()
    })
  }
}</script><script>(()=>{
  const $countDom = document.getElementById('twikoo-count')
  const init = () => {
    twikoo.init(Object.assign({
      el: '#twikoo-wrap',
      envId: 'https://twikoo-beta-tawny.vercel.app/',
      region: ''
    }, null))
  }

  const getCount = () => {
    twikoo.getCommentsCount({
      envId: 'https://twikoo-beta-tawny.vercel.app/',
      region: '',
      urls: [window.location.pathname],
      includeReply: false
    }).then(function (res) {
      $countDom.innerText = res[0].count
    }).catch(function (err) {
      console.error(err);
    });
  }

  const loadTwikoo = (bool = false) => {
    if (typeof twikoo === 'object') {
      init()
      bool && $countDom && setTimeout(getCount,0)
    } else {
      getScript('https://cdn.jsdelivr.net/npm/twikoo/dist/twikoo.all.min.js').then(()=> {
        init()
        bool && $countDom && setTimeout(getCount,0)
      })
    }
  }

  if ('Twikoo' === 'Twikoo' || !false) {
    if (false) btf.loadComment(document.getElementById('twikoo-wrap'), loadTwikoo)
    else loadTwikoo(true)
  } else {
    window.loadOtherComment = () => {
      loadTwikoo()
    }
  }
})()</script></div><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>